#!/usr/bin/python
#coding:utf-8
#File Name: connect_mysql.py
#Author: moshan
#Mail: mo_shan@yeah.net
#Created Time: 2019-01-10 10:46:03
#Function: 与mysql建立长连接
#########################################################################

import pymysql, time, datetime, sys
import logging
logger = logging.getLogger(__name__)
logger.setLevel(level = logging.INFO)
handler = logging.FileHandler("/home/moshan/zabbix_monitor/select.log")
handler.setLevel(logging.INFO)
formatter = logging.Formatter('[%(asctime)s] [%(levelname)s] [%(message)s]')
handler.setFormatter(formatter)

console = logging.StreamHandler()
console.setLevel(logging.INFO)

logger.addHandler(handler)
logger.addHandler(console)
mysql_host="192.168.1.xxx"
mysql_user="moshan"
mysql_password="xxxxxxx"
mysql_db="test"
mysql_port=3307

#打开数据库连接
db = pymysql.connect(mysql_host, mysql_user, mysql_password, mysql_db, mysql_port)

#使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

while True:
    try :
        cursor.execute("select count(*),1 from dba_test")
        db.commit()
        mysql_count = cursor.fetchone()
    except :
        #对于连接被kill, 或者被超时退出, 则进行重连处理
        mysql_count = (10000,)
        db.close()
        db = pymysql.connect(mysql_host, mysql_user, mysql_password, mysql_db, mysql_port)
        cursor = db.cursor()
    #使用fetchall()方法获取数据
    if mysql_count[0] == -1 :
        info = str(mysql_count[0]) + " error"
    else :
        info = str(mysql_count[0]) + " ok"
    logger.info(info)
    #logger.debug("Do something")
    #logger.warning("Something maybe fail.")
    time.sleep(1)

#关闭数据库连接
db.close()
